insert overwrite table jms_dm.dm_pre_rate_agent_summary_dt partition(dt)
select
  date_time,
  data_type,
  if(island_code in ('D608','D09','D607','D67','D801','D619','D190') ,island_code,'' ) island_code,
  case when island_code in ('D608','D09','D607') then '桃花岛' 
       when island_code = 'D67' then '紫金山'
       when island_code = 'D190' then '逍遥峰' 
       when island_code = 'D801' then '七星潭' 
       when island_code = 'D619' then '极地湾'
  else '其他' end island_name,
  start_subordinate_agent_code,
  start_subordinate_agent_name,
  sign_subordinate_agent_code,
  sign_subordinate_agent_name,
  start_net.virt_code,
  start_net.virt_name,
  sign_net.virt_code,
  sign_net.virt_name,
  plan_pdd_date,
  plan_mountain_date,
  plan_free_date,
  plan_jd_date,
  plan_cainiao_date,
  plan_cainiao_pro_date,
  plan_mountain_pro_date,
  if(districtprescription in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0) is_districtprescription,
  sum(real_reach_sum) real_reach_sum,
  sum(real_total) real_total,
  sum(pres_reach_sum) pres_reach_sum,
  sum(pres_total) pres_total,
  if(platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0) is_platform_effect,
  sum(real_platform_reach_sum) real_platform_reach_sum,
  sum(real_platform_total) real_platform_total,
  sum(pres_platform_reach_sum) pres_platform_reach_sum,
  sum(pres_platform_total) pres_platform_total,
  if(mountain_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0) is_mountain_platform_effect,
  sum(mountain_real_platform_reach_sum) mountain_real_platform_reach_sum,
  sum(mountain_real_platform_total) mountain_real_platform_total,
  sum(mountain_pres_platform_reach_sum) mountain_pres_platform_reach_sum,
  sum(mountain_pres_platform_total) mountain_pres_platform_total,
  if(free_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0) is_free_platform_effect,
  sum(free_real_platform_reach_sum) free_real_platform_reach_sum,
  sum(free_real_platform_total) free_real_platform_total,
  sum(free_pres_platform_reach_sum) free_pres_platform_reach_sum,
  sum(free_pres_platform_total) free_pres_platform_total,
  if(jd_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0) is_jd_platform_effect,
  sum(jd_real_platform_reach_sum) jd_real_platform_reach_sum,
  sum(jd_real_platform_total) jd_real_platform_total,
  sum(jd_pres_platform_reach_sum) jd_pres_platform_reach_sum,
  sum(jd_pres_platform_total) jd_pres_platform_total,
  if(cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0) is_cainiao_platform_effect,
  sum(cainiao_real_platform_reach_sum) cainiao_real_platform_reach_sum,
  sum(cainiao_real_platform_total) cainiao_real_platform_total,
  sum(cainiao_pres_platform_reach_sum) cainiao_pres_platform_reach_sum,
  sum(cainiao_pres_platform_total) cainiao_pres_platform_total,
  if(cainiao_pro_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0) is_cainiao_pro_platform_effect,
  sum(cainiao_pro_real_platform_reach_sum) cainiao_pro_real_platform_reach_sum,
  sum(cainiao_pro_real_platform_total) cainiao_pro_real_platform_total,
  sum(cainiao_pro_pres_platform_reach_sum) cainiao_pro_pres_platform_reach_sum,
  sum(cainiao_pro_pres_platform_total) cainiao_pro_pres_platform_reach_sum,
  if(mountain_pro_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0) is_mountain_pro_platform_effect,
  sum(mountain_pro_real_platform_reach_sum) mountain_pro_real_platform_reach_sum,
  sum(mountain_pro_real_platform_total) mountain_pro_real_platform_total,
  sum(mountain_pro_pres_platform_reach_sum) mountain_pro_pres_platform_reach_sum,
  sum(mountain_pro_pres_platform_total) mountain_pro_pres_platform_total,
  sum(real_zeroday_num) real_zeroday_num,
  sum(real_oneday_num) real_oneday_num,
  sum(real_twoday_num) real_twoday_num,
  sum(real_threeday_num) real_threeday_num,
  sum(real_four_num) real_four_num,
  sum(real_five_num) real_five_num,
  sum(real_six_num) real_six_num,
  sum(pres_zeroday_num) pres_zeroday_num,
  sum(pres_oneday_num) pres_oneday_num,
  sum(pres_twoday_num) pres_twoday_num,
  sum(pres_threeday_num) pres_threeday_num,
  sum(pres_four_num) pres_four_num,
  sum(pre_five_num) pres_five_num,
  sum(pre_six_num) pres_six_num,
  sum(jd_real_one_num) jd_real_one_num,
  sum(jd_real_two_num) jd_real_two_num,
  sum(jd_real_three_num) jd_real_three_num,
  sum(jd_real_four_num) jd_real_four_num,
  sum(jd_real_five_num) jd_real_five_num,
  sum(jd_real_six_num) jd_real_six_num,
  sum(jd_real_seven_num) jd_real_seven_num,
  sum(jd_pre_one_num) jd_pre_one_num,
  sum(jd_pre_two_num) jd_pre_two_num,
  sum(jd_pre_three_num) jd_pre_three_num,
  sum(jd_pre_four_num) jd_pre_four_num,
  sum(jd_pre_five_num) jd_pre_five_num,
  sum(jd_pre_six_num) jd_pre_six_num,
  sum(jd_pre_seven_num) jd_pre_seven_num,
  dt
from
(
select *,1 as data_type,
null plan_pdd_date,
null plan_mountain_date,
null plan_free_date,
null plan_jd_date,
null plan_cainiao_date,
null plan_cainiao_pro_date,
null plan_mountain_pro_date 
from jms_dm.dm_net_pre_reach_rate_dt
-- from jms_dm.dm_agency_province_pre_reach_rate_dt
where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
and '{{ execution_date | cst_ds }}'
and (is_pres_sign = '1' or is_pres_sign = '0')
and date_time is not null
union all 
select *,2 as data_type, 
null plan_pdd_date,
null plan_mountain_date,
null plan_free_date,
null plan_jd_date,
null plan_cainiao_date,
null plan_cainiao_pro_date,
null plan_mountain_pro_date
from jms_dm.dm_net_pre_reach_rate_sign_dt
-- from jms_dm.dm_agency_province_pre_reach_rate_sign_dt
where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1)
and '{{ execution_date | cst_ds }}'
and (is_pres_sign = '1' or is_pres_sign = '0')
and date_time is not null
union all 
select *,3 as data_type,
plan_platform_date plan_pdd_date,
plan_mountain_platform_date plan_mountain_date,
plan_free_platform_date plan_free_date,
plan_jd_platform_date plan_jd_date,
plan_cainiao_platform_date plan_cainiao_date,
plan_cainiao_pro_platform_date plan_cainiao_pro_date,
plan_mountain_pro_platform_date plan_mountain_pro_date
from jms_dm.dm_net_pre_reach_rate_plan_dt
-- from jms_dm.dm_agency_province_pre_reach_rate_plan_dt
where dt between date_add('{{ execution_date | cst_ds }}', -11 + 1) and date_add('{{ execution_date | cst_ds }}',3)
and (is_pres_sign = '1' or is_pres_sign = '0')
and date_time is not null
) detail left join jms_dim.dim_network_whole_massage start_net
on detail.start_subordinate_agent_code = start_net.code
left join jms_dim.dim_network_whole_massage sign_net
on detail.sign_subordinate_agent_code = sign_net.code
group by 
  dt,
  date_time,
  data_type,
  start_net.virt_code,
  start_net.virt_name,
  sign_net.virt_code,
  sign_net.virt_name,
  if(island_code in ('D608','D09','D607','D67','D801','D619','D190') ,island_code,'' )   ,
  case when island_code in ('D608','D09','D607') then '桃花岛' 
       when island_code = 'D67' then '紫金山'
       when island_code = 'D190' then '逍遥峰' 
       when island_code = 'D801' then '七星潭' 
       when island_code = 'D619' then '极地湾'
  else '其他' end ,
  start_subordinate_agent_code,
  start_subordinate_agent_name,
  sign_subordinate_agent_code,
  sign_subordinate_agent_name,
  if(districtprescription in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0),
  if(platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0),
  if(mountain_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0),
  if(free_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0),
  if(jd_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0),
  if(cainiao_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0),
  if(cainiao_pro_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0),
  if(mountain_pro_platform_effect in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),1,0),
  plan_pdd_date,
  plan_mountain_date,
  plan_free_date,
  plan_jd_date,
  plan_cainiao_date,
  plan_cainiao_pro_date,
  plan_mountain_pro_date
distribute by dt,1;